/***********************************************************************************************************************************
 * Name:	Fiscal_Schedule.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	Add job schedules for the first Monday of each fiscal month.
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 0 < @@TRANCOUNT ROLLBACK;
USE ARData;
GO
DECLARE
  @fiscalYearMonthMonday	NCHAR(26),
  @active_start_date		INT;

DECLARE list CURSOR LOCAL FAST_FORWARD FOR
	WITH fm(fiscalYearMonth,firstMonday) AS (
	  SELECT fiscalYearMonth, MIN(day)
		FROM FiscalTime
		WHERE calendarYear = 2013 AND dayOfWeek = 'Monday'
		GROUP BY fiscalYearMonth)
	SELECT N'Fiscal '+CAST(fiscalYearMonth AS NCHAR(6))+N' First Monday', active_start_date= DATEPART(yy,firstMonday)*10000 + DATEPART(mm,firstMonday)*100 + DATEPART(dd,firstMonday)
	FROM fm ORDER BY fiscalYearMonth;

OPEN list;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @fiscalYearMonthMonday, @active_start_date;
  IF @@FETCH_STATUS <> 0 BREAK;
 -- EXEC msdb.dbo.sp_add_jobschedule
	@job_id				= N'79209e10-ec30-4313-a9ac-a94f95aca0ca',
	@name				= @fiscalYearMonthMonday, 
	@freq_type			= 1,
	@active_start_date	= @active_start_date,
	@active_start_time	= 110000;
  EXEC msdb.dbo.sp_add_jobschedule
	@job_id				= N'229870be-75bd-4d3c-a09c-a41c2959c861',
	@name				= @fiscalYearMonthMonday, 
	@freq_type			= 1,
	@active_start_date	= @active_start_date,
	@active_start_time	= 143000;
END;
CLOSE list;
DEALLOCATE list;
GO